Skip Headers

Oracle® Database Application Developer's Guide - Object-Relational Features 10g Release 2 (10.2) Part Number B14260-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 1 Introduction to Oracle Objects

This chapter describes the advantages and key features of the Oracle object-relational model. The chapter contains these topics:

- About Oracle Objects

- Advantages of Objects

- Key Features of the Object-Relational Model

## About Oracle Objects

Oracle object types are user-defined types that make it possible to model real-world entities such as customers and purchase orders as objects in the database.

Oracle object technology is a layer of abstraction built on Oracle relational technology. New object types can be created from any built-in database types and any previously created object types, object references, and collection types. Metadata for user-defined types is stored in a schema that is available to SQL, PL/SQL, Java, and other published interfaces.

Object types and related object-oriented features such as variable-length arrays and nested tables provide higher-level ways to organize and access data in the database. Underneath the object layer, data is still stored in columns and tables, but you are able to work with the data in terms of the real-world entities, such as customers and purchase orders, that make the data meaningful. Instead of thinking in terms of columns and tables when you query the database, you can simply select a customer.

Internally, statements about objects are still basically statements about relational tables and columns, and you can continue to work with relational data types and store data in relational tables as before. But now you have the option to take advantage of object-oriented features too. You can begin to use object-oriented features while continuing to work with most of your data relationally, or you can go over to an object-oriented approach entirely. For instance, you can define some object data types and store the objects in columns in relational tables, which enables you to extend the system built-in types with user-defined ones. You can also create object views of existing relational data to represent and access this data according to an object model. Or you can store object data in object tables, where each row is an object.

## Advantages of Objects

In general, the object-type model is similar to the class mechanism found in C++ and Java. Like classes, objects make it easier to model complex, real-world business entities and logic, and the reusability of objects makes it possible to develop database applications faster and more efficiently. By natively supporting object types in the database, Oracle enables application developers to directly access the data structures used by their applications. No mapping layer is required between client-side objects and the relational database columns and tables that contain the data. Object abstraction and the encapsulation of object behaviors also make applications easier to understand and maintain.

Below are listed several other specific advantages that objects offer over a purely relational approach.

Objects Can Encapsulate Operations Along with Data

Database tables contain only data. Objects can include the ability to perform operations that are likely to be needed on that data. Thus a purchase order object might include a method to sum the cost of all the items purchased. Or a customer object might have methods to return the customer's buying history and payment pattern. An application can simply call the methods to retrieve the information.

Objects Are Efficient

Using object types makes for greater efficiency:

- Object types and their methods are stored with the data in the database, so they are available for any application to use. Developers can benefit from work that is already done and do not need to re-create similar structures in every application.

- You can fetch and manipulate a set of related objects as a single unit. A single request to fetch an object from the server can retrieve other objects that are connected to it. For example, when you select a customer object and get the customer's name, phone, and the multiple parts of his address in a single round-trip between the client and the server. When you reference a column of a SQL object type, you retrieve the whole object.

Objects Can Represent Part-Whole Relationships

In a relational system, it is awkward to represent complex part-whole relationships. A piston and an engine have the same status in a table for stock items. To represent pistons as parts of engines, you must create complicated schemas of multiple tables with primary key-foreign key relationships. Object types, on the other hand, give you a rich vocabulary for describing part-whole relationships. An object can have other objects as attributes, and the attribute objects can have their own object attributes too. An entire parts-list hierarchy can be built up in this way from interlocking object types.

## Key Features of the Object-Relational Model

Oracle implements the object-type system as an extension of the relational model. The object-type interface continues to support standard relational database functionality such as queries (`SELECT…FROM…WHERE`), fast commits, backup and recovery, scalable connectivity, row-level locking, read consistency, partitioned tables, parallel queries, cluster database, export and import, and loader. Plus SQL and various programmatic interfaces to Oracle; including PL/SQL, Java, Oracle Call Interface, Pro*C/C++, and OO4O; have been enhanced with new extensions to support objects. The result is an object-relational model, which offers the intuitiveness and economy of an object interface while preserving the high concurrency and throughput of a relational database.

### Core Database Key Features

This section lists the key features and concepts of the object-relational model that are related to the database. Figure 1-1 shows an object type and instances of the object.

Figure 1-1 An Object Type and Object Instances

Description of the illustration adobj036.gif

#### Object Types

An object type is a kind of datatype. You can use it in the same ways that you use more familiar datatypes such as `NUMBER` or `VARCHAR2`. For example, you can specify an object type as the datatype of a column in a relational table, and you can declare variables of an object type. You use a variable of an object type to contain a value of that object type. A value of an object type is an instance of that type. An object instance is also called an object. Example 1-1 shows how to create an object type named `person_typ`. In the example, an object specification and object body are defined. For information on the `CREATE` `TYPE` SQL statement, see Oracle Database SQL Reference. For information on the `CREATE` `TYPE` `BODY` SQL statement, see Oracle Database SQL Reference.

Example 1-1 Creating the person_typ Object

    CREATE TYPE person_typ AS OBJECT (
      idno           NUMBER,
      first_name     VARCHAR2(20),
      last_name      VARCHAR2(25),
      email          VARCHAR2(25),
      phone          VARCHAR2(20),
      MAP MEMBER FUNCTION get_idno RETURN NUMBER,
      MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ));
    /

    CREATE TYPE BODY person_typ AS
      MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
      BEGIN
        RETURN idno;
      END;
      MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ) IS
      BEGIN
        -- use the PUT_LINE procedure of the DBMS_OUTPUT package to display details
        DBMS_OUTPUT.PUT_LINE(TO_CHAR(idno) || ' ' || first_name || ' ' || last_name);
        DBMS_OUTPUT.PUT_LINE(email || ' '  || phone);
      END;
    END;
    /

Object types have some important differences from the more familiar datatypes that are native to a relational database:

- A set of object types does not come ready-made with the database. Instead, you define the specific object types you want by extending built-in types with user-defined ones as shown in Example 1-1.

- Object types are composed of parts, called attributes and methods, illustrated in Figure 1-2.

  - Attributes hold the data about an object's features of interest. For example, a `student` object type might have `name`, `major`, and `graduatio`n `date attributes`. An attribute has a declared datatype which can in turn be another object type. Taken together, the attributes of an object instance contain that object's data.

  - Methods are procedures or functions provided to enable applications to perform useful operations on the attributes of the object type. Methods are an optional element of an object type. They define the behavior of objects of that type and determine what (if anything) that type of object can do.

Figure 1-2 Object Attributes and Methods

Description of the illustration adobj035.gif

Object types are less generic than native datatypes. In fact, this is one of their major virtues. You can define object types to model the actual structure of the real-world entities, such as customers and purchase orders, that application programs deal with. This can make it easier and more intuitive to manage the data for these entities. In this respect object types are like Java and C++ classes.

You can think of an object type as a blueprint or template which defines structure and behavior. An instantiation of the object type creates an object built according to the template. Object types are database schema objects, subject to the same kinds of administrative control as other schema objects.

You can use object types to model the actual structure of real-world objects. Object types enable you to capture the structural interrelationships of objects and their attributes instead of flattening this structure into a two-dimensional, purely relational schema of tables and columns. With object types you can store related pieces of data in a unit along with the behaviors defined for that data. Application code can then retrieve and manipulate these units as objects. See Chapter 6, "Managing Oracle Objects".

#### Objects

When you create a variable of an object type, you create an instance of the type and the result is an object. An object has the attributes and methods defined for its type. Because an object instance is a concrete thing, you can assign values to its attributes and call its methods.

You use the `CREATE` `TYPE` statement to define object types. In Example 1-1, the `CREATE` `TYPE` statement define the object type `person_typ`.

The indented elements `idno`, `name`, and `phone` in the `CREATE` `TYPE` statements are attributes. Each has a datatype declared for it. These are simplified examples and do not show how to specify member methods.

Defining an object type does not allocate any storage. After they are defined, object types can be used in SQL statements in most of the same places you can use types like `NUMBER` or `VARCHAR2`.

For example, you might define a relational table to keep track of your contacts, as shown in Example 1-2.

Example 1-2 Creating the contacts Table with an Object Type Column

    CREATE TABLE contacts (
      contact         person_typ,
      contact_date    DATE );

    INSERT INTO contacts VALUES (
      person_typ (65, 'Verna', 'Mills', 'vmills@oracle.com', '1-800-555-4412'),
     '24 Jun 2003' );

The `contacts` table is a relational table with an object type as the datatype of one of its columns. Objects that occupy columns of relational tables are called column objects. See "Row Objects and Column Objects".

#### Object Methods

Methods are functions or procedures that you can declare in an object type definition to implement behavior that you want objects of that type to perform. For example, a method is declared in Example 1-1 to allow comparisons between `person_typ` objects.

The general kinds of methods that can be declared in a type definition are:

- Member

- Static

- Constructor

A principal use of methods is to provide access to the data of an object. You can define methods for operations that an application is likely to want to perform on the data so that the application does not have to code these operations itself. To perform the operation, an application calls the appropriate method on the appropriate object.

In Example 1-3, the SQL statement uses the `get_idno()` method to display the Id number of persons in the `contacts` table:

Example 1-3 Using the get_idno Object Method

    SELECT c.contact.get_idno() FROM contacts c;

You can also define static methods to compare object instances and to perform operations that do not use any particular object's data but instead are global to an object type.

A constructor method is implicitly defined for every object type, unless this default constructor is over-written with a user-defined constructor. A constructor method is called on a type to construct or create an object instance of the type. See "Object Methods".

#### Type Inheritance

Type inheritance adds to the usefulness of objects by enabling you to create type hierarchies by defining successive levels of increasingly specialized subtypes that derive from a common ancestor object type, which is called a supertype of the derived types. Derived subtypes inherit the features of the parent object type but extend the parent type definition. The specialized types can add new attributes or methods, or redefine methods inherited from the parent. The resulting type hierarchy provides a higher level of abstraction for managing the complexity of an application model.

For example, specialized types of persons, such as a student type or a part-time student type with additional attributes or methods, might be derived from a general person object type. See "Inheritance in SQL Object Types".

#### Type Evolution

Using an `ALTER` `TYPE` statement, you can modify, or evolve, an existing object type to make the following changes:

- Add and drop attributes

- Add and drop methods

- Modify a numeric attribute to increase its length, precision, or scale

- Modify a varying length character attribute to increase its length

- Change a type's `FINAL` and `INSTANTIABLE` properties

Dependencies of a type to be altered are checked using essentially the same validations applied for a `CREATE` `TYPE` statement. If a type or any of its dependent types fails the type validations, the `ALTER` `TYPE` statement rolls back.

Metadata for all tables and columns that use an altered type are updated for the new type definition so that data can be stored in them in the new format. Existing data can be converted to the new format either all at once or piecemeal, as it is updated. In either case, data is always presented in the new type definition even if it is still stored in the format of the older one.

#### Object Tables

An object table is a special kind of table in which each row represents an object. In Example 1-4 the statement creates an object table for `person_typ` objects.

Example 1-4 Creating the person_obj_table Object Table

    CREATE TABLE person_obj_table OF person_typ;

You can view this table in two ways:

- As a single-column table in which each row is a `person_typ` object, allowing you to perform object-oriented operations

- As a multi-column table in which each attribute of the object type `person_typ`; such as `idno`, `name`, and `phone`; occupies a column, allowing you to perform relational operations

Example 1-5 illustrates several operations on an object table.

Example 1-5 Operations on the person_obj_table Object Table

    INSERT INTO person_obj_table VALUES (
           person_typ(101, 'John', 'Smith', 'jsmith@oracle.com', '1-800-555-1212') );

    SELECT VALUE(p) FROM person_obj_table p
            WHERE p.last_name = 'Smith';

    DECLARE
      person person_typ;
    BEGIN -- PL/SQL block for selecting a person and displaying details
      SELECT VALUE(p) INTO person FROM person_obj_table p WHERE p.idno = 101;
      person.display_details();
    END;
    /

The first SQL statement in Example 1-5 inserts a `person_typ` object into `person_obj_table`, treating `person_table` as a multi-column table. The second SQL statement selects from `person_obj_table` as a `single-column` table, using the `VALUE` function to return rows as object instances. See "VALUE" for information on the `VALUE` function.

The PL/SQL block in Example 1-5 selects a specific person and executes a member function of `person_typ` to display details about the specified person. For more information on the use of PL/SQL with objects, see Oracle Database PL/SQL User's Guide and Reference.

By default, every row object in an object table has an associated logical object identifier (OID) that uniquely identifies it in an object table. In a distributed and replicated environment, the system-generated unique identifier lets Oracle identify objects unambiguously. See "Storage Considerations for Object Identifiers (OIDs)" for information on Object Identifiers and using `REF`s to OIDs.

#### Row Objects and Column Objects

Objects that are stored in complete rows in object tables are called row objects. Objects that are stored as columns of a table in a larger row, or are attributes of other objects, are called column objects.

#### Object Views

An object view is a way to access relational data using object-relational features. It lets you develop object-oriented applications without changing the underlying relational schema.

Oracle allows the creation of an object abstraction over existing relational data through the object view mechanism. You access objects that belong to an object view in the same way that you access row objects in an object table. Oracle also supports materialized view objects of user-defined types from data stored in relational schemas and tables. By using object views, you can develop object-oriented applications without having to modify existing relational database schemas.

Object views also let you exploit the polymorphism that a type hierarchy makes possible. A polymorphic expression can take a value of the expression's declared type or any of that type's subtypes. If you construct a hierarchy of object views that mirrors some or all of the structure of a type hierarchy, you can query any view in the hierarchy to access data at just the level of specialization you are interested in. If you query an object view that has subviews, you can get back polymorphic data—rows for both the type of the view and for its subtypes. See Chapter 5, "Applying an Object Model to Relational Data".

#### References

A `REF` is a logical pointer to a row object that is constructed from the object identifier (OID) of the referenced object and is an Oracle built-in datatype. `REF`s and collections of `REF`s model associations among objects, particularly many-to-one relationships, thus reducing the need for foreign keys. `REF`s provide an easy mechanism for navigating between objects. You can use the dot notation to follow the pointers. Oracle does joins for you when needed, and in some cases can avoid doing joins.

You can use a `REF` to examine or update the object it refers to. You can also use a `REF` to obtain the object it refers to. You can change a `REF` so that it points to a different object of the same object type hierarchy or assign it a null value.

Example 1-6 illustrates a simple use of a `REF`.

Example 1-6 Using a REF to the emp_person_typ Object

    CREATE TYPE emp_person_typ AS OBJECT (
      name     VARCHAR2(30),
      manager  REF emp_person_typ );
    /
    CREATE TABLE emp_person_obj_table OF emp_person_typ;

    INSERT INTO emp_person_obj_table VALUES (
       emp_person_typ ('John Smith', NULL));

    INSERT INTO emp_person_obj_table
      SELECT emp_person_typ ('Bob Jones', REF(e))
        FROM emp_person_obj_table e
        WHERE e.name = 'John Smith';

See "Rules for REF Columns and Attributes" and "Design Considerations for REFs".

Scoped REFs

In declaring a column type, collection element, or object type attribute to be a `REF`, you can constrain it to contain only references to a specified object table. Such a `REF` is called a scoped `REF`. Scoped `REF` types require less storage space and allow more efficient access than unscoped `REF` types.

Example 1-7 shows `REF` column `contact_ref` scoped to `person_obj_table` which is an object table of type `person_typ`.

Example 1-7 Creating the contacts_ref Table Using a Scoped REF

    CREATE TABLE contacts_ref (
      contact_ref   REF person_typ SCOPE IS person_obj_table,
      contact_date  DATE );

To insert a row in the table, you could issue the following:

    INSERT INTO contacts_ref
      SELECT REF(p), '26 Jun 2003'
        FROM person_obj_table p
        WHERE p.idno = 101;

A `REF` can be scoped to an object table of the declared type (`person_typ` in the example) or of any subtype of the declared type. If scoped to an object table of a subtype, the `REF` column is effectively constrained to hold references only to instances of the subtype (and its subtypes, if any) in the table. See "Inheritance in SQL Object Types".

Dangling REFs

It is possible for the object identified by a `REF` to become unavailable through either deletion of the object or a revoking of privileges. Such a `REF` is called dangling. Oracle SQL provides a predicate (called `IS` `DANGLING`) to allow testing `REF`s for this condition.

Dangling `REF`s can be avoided by defining referential integrity constraints. See "Rules for REF Columns and Attributes".

Dereferencing REFs

Accessing the object referred to by a `REF` is called dereferencing the `REF`. Oracle provides the `DEREF` operator to do this. Dereferencing a dangling `REF` returns a null object, as shown in Example 1-8.

Example 1-8 Using DEREF to Dereference a REF

    SELECT DEREF(c.contact_ref), c.contact_date FROM contacts_ref c;

Oracle also provides implicit dereferencing of REFs. For example, to access the manager's name for an employee, you can use a SQL expression similar to the following:

    SELECT e.name, e.manager.name FROM emp_person_obj_table e
      WHERE e.name = 'Bob Jones';

In the example, `e.manager.name` follows the pointer from the person's manager, and retrieves the manager's name. Following the `REF` like this is allowed in SQL, but not in PL/SQL.

Obtaining REFs

You can obtain a `REF` to a row object by selecting the object from its object table and applying the `REF` operator. You can obtain a `REF` to the person with `idno` equal to 1 as shown in Example 1-9.

Example 1-9 Obtaining a REF to a Row Object

    DECLARE
      person_ref REF person_typ;
    BEGIN
      SELECT REF(p) INTO person_ref
        FROM person_obj_table p
        WHERE p.idno = 101;
    END;/

The query must return exactly one row. See "Storage Size of REFs".

#### Collections

For modeling multi-valued attributes and many to many relationships, Oracle supports two collection datatypes: varrays and nested tables. Collection types can be used anywhere other datatypes can be used. You can have object attributes of a collection type in addition to columns of a collection type. For example, you might give a purchase order object type a nested table attribute to hold the collection of line items for a given purchase order.

You use the `CREATE` `TYPE` statement to define collection types. In Example 1-10, the `CREATE` `TYPE` statements define the object types `people_typ` and `dept_persons_typ`.

Example 1-10 Creating the people_typ Collection Datatype

    CREATE TYPE people_typ AS TABLE OF person_typ;
    /

    CREATE TYPE dept_persons_typ AS OBJECT (
      dept_no    CHAR(5),
      dept_name  CHAR(20),
      dept_mgr   person_typ,
      dept_emps  people_typ);
    /

In this simplified example, `people_typ` is a collection type, specifically a nested table type. The `dept_persons_typ` object type has an attribute `people_typ` of this type. Each row in the people_typ nested table is an object of type `person_typ` which was defined in Example 1-1. See "Creating Collection Datatypes".

### Language Binding Features

This section lists the key features of the object-relational model that are related to languages and application programming interfaces (APIs).

SQL Object Extensions

To support the new object-related features, SQL extensions, including new DDL, have been added to create, alter, or drop object types; to store object types in tables; and to create, alter, or drop object views. There are DML and query extensions to support object types, references, and collections. See"SQL".

PL/SQL Object Extensions

PL/SQL is an Oracle database programming language that is tightly integrated with SQL. With the addition of object types and other SQL types, PL/SQL has been enhanced to operate on object types seamlessly. Thus, application developers can use PL/SQL to implement logic and operations on user-defined types that execute in the database server. See "PL/SQL".

Java Support for Oracle Objects

Oracle Java VM is tightly integrated with the RDBMS and supports access to Oracle Objects through object extensions to Java Database Connectivity (JDBC), which provides dynamic SQL, and SQLJ, which provides static SQL. Thus, application developers can use the Java to implement logic and operations on object types that execute in the database server. With Oracle, you can now also create SQL types mapped to existing Java classes to provide persistent storage for Java objects using SQLJ object types where all the methods are implemented in their corresponding Java classes. See "Java Object Storage".

See Also: Oracle Database JDBC Developer's Guide and Reference

External Procedures

Database functions, procedures, or member methods of an object type can be implemented in PL/SQL, Java, or C as external procedures. External procedures are best suited for tasks that are more quickly or easily done in a low-level language such as C, which is more efficient at machine-precision calculation. External procedures are always run in a safe mode outside the address space of the RDBMS server. Generic external procedures can be written that declare one or more parameters to be of a system-defined generic type. The generic type permits a procedure that uses it to work with data of any built-in or user-defined type.

Object Type Translator/JPublisher

Object Type Translator (OTT) and Oracle JPublisher provide client-side mappings to object type schemas by using schema information from the Oracle data dictionary to generate header files containing Java classes and C structures and indicators. These generated header files can be used in host-language applications for transparent access to database objects.

Client-Side Cache

Oracle provides an object cache for efficient access to persistent objects stored in the database. Copies of objects can be brought into the object cache. Once the data has been cached in the client, the application can traverse through these at memory speed. Any changes made to objects in the cache can be committed to the database by using the object extensions to Oracle® Call Interface programmatic interfaces.

Oracle Call Interface and Oracle C++ Call Interface

Oracle Call Interface (OCI) and Oracle C++ Call Interface provide a comprehensive application programming interface for application and tool developers seeking to use the object capabilities of Oracle. Oracle Call Interface provides a run-time environment with functions to connect to an Oracle server, and control transactions that access objects in the server. It allows application developers to access and manipulate objects and their attributes in the client-side object cache either navigationally, by traversing a graph of inter-connected objects, or associatively by specifying the nature of the data through declarative SQL DML. Oracle Call Interface also provides a number of functions for accessing metadata information at run-time about object types defined in the server. Such a set of functions facilitates dynamic access to the object metadata and the actual object data stored in the database. See "Oracle Call Interface (OCI)" and "Oracle C++ Call Interface (OCCI)".

Pro*C/C++ Object Extensions

The Oracle Pro*C™ precompiler provides an embedded SQL application programming interface and offers a higher level of abstraction than Oracle Call Interface. Like Oracle Call Interface, the Pro*C precompiler allows application developers to use the Oracle client-side object cache and the Object Type Translator Utility. Pro*C supports the use of C bind variables for Oracle object types. Furthermore, Pro*C provides new simplified syntax to allocate and free objects of SQL types and access them by either SQL DML, or through the navigational interface. Thus, it provides application developers many benefits, including compile-time type checking of (client-side) bind variables against the schema in the server, automatic mapping of object data in an Oracle server to program bind variables in the client, and simple ways to manage and manipulate database objects in the client process. See "Oracle Call Interface (OCI)".

OO4O Object Extensions

Oracle Objects For OLE (OO4O) is a set of COM Automation interfaces/objects for connecting to Oracle database servers, executing queries and managing the results. Automation interfaces in OO4O provide easy and efficient access to Oracle features and can be used from virtually any programming or scripting language that supports the Microsoft COM Automation technology. This includes Visual Basic, Visual C++, VBA in Excel, VBScript and JavaScript in IIS Active Server Pages. See "Oracle Objects For OLE (OO4O)".